package teacher.service;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import teacher.bean.Award;
import teacher.bean.Students; 
import teacher.bean.Stuaward;
import teacher.bean.Stuproject;
import teacher.bean.Studegreepaper;
import teacher.bean.Teacher;
import teacher.page.Page;

import database.dao.DBConnection;
import database.dao.MysqlCrud;
import tools.StringDate;


public class student {
	/**
	 * @category 插入学生数据
	 */
	public boolean insertStu(Students s){
		boolean flag = false;
		String sql="insert into students(name,number,sex,degree,isleave,leaveDate,teaId) values(?,?,?,?,?,?,?)";
		String[] parameter={
				String.valueOf(s.getName()),
				String.valueOf(s.getNumber()),
				String.valueOf(s.getSex()),
				String.valueOf(s.getDegree()),
				String.valueOf(s.getIsleave()),
				String.valueOf(StringDate.DateToString(s.getLeaveDate())),
				String.valueOf(s.getTeaId())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		flag = mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 插入学生项目
	 */
	public boolean insertStuPro(Stuproject s){
		boolean flag = false;
		String sql="insert into stuproject(proname,prolevel,prosource,stuId)"+"values(?,?,?,?)";
		String[] parameter={
				String.valueOf(s.getProname()),
				String.valueOf(s.getProlevel()),
				String.valueOf(s.getProsource()),
				String.valueOf(s.getStuId())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		flag = mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 插入学生获奖
	 */
	public boolean insertStuAwa(Stuaward s){
		boolean flag = false;
		String sql="insert into stuaward(awaname,awalevel,awakind,department,stuId)"+"values(?,?,?,?,?)";
		String[] parameter={
				String.valueOf(s.getAwaname()),
				String.valueOf(s.getAwalevel()),
				String.valueOf(s.getAwakind()),
				String.valueOf(s.getDepartment()),
				String.valueOf(s.getStuId())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		flag = mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 插入学位论文
	 */
	public boolean insertDeg(Studegreepaper s){
		boolean flag = false;
		String sql="insert into studegreepaper(degname,degaward,upload,stuId)"+"values(?,?,?,?)";
		String[] parameter={
				String.valueOf(s.getDegname()),
				String.valueOf(s.getDegaward()),
				String.valueOf(s.getUpload()),
				String.valueOf(s.getStuId())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		flag = mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 通过老师查询学生
	 */
	public List<Students> selectStudentByTeacher(Teacher tea,Page page) {
		String sql= "select * from students where teaId=? limit ?,?";
		int i=0;
		List<Students> studentOne=new ArrayList<Students>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		Integer[] parameter={tea.getTeaId(),page.getBeginIndex(),page.getEveryPage()};
		List<Map> list = mysqlcurd.SelectDataInt(sql, parameter);
		while(i<list.size()){
			Map map=list.get(i);
			try {
				Students s = new Students();
				s.setStuId(Integer.parseInt(map.get(0).toString()));
				s.setName(map.get(1).toString());
				s.setNumber(map.get(2).toString());
				s.setSex(Integer.parseInt(map.get(3).toString()));
				s.setDegree(Integer.parseInt(map.get(4).toString()));
				s.setIsleave(Integer.parseInt(map.get(5).toString()));
				s.setLeaveDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(6).toString()));		
				s.setTeaId(Integer.parseInt(map.get(7).toString()));
				studentOne.add(s);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			i++;
		}
		return studentOne;
	}
	
	/**
	 * @category 模糊查询学生
	 */
	public List<Students> selectStudentByString(Teacher tea,String stu,Page page) {
		String sql= "select * from students where teaId=? and name like '%"+stu+"%' limit ?,?";
		int i=0;
		List<Students> studentOne=new ArrayList<Students>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		Integer[] parameter={tea.getTeaId(),page.getBeginIndex(),page.getEveryPage()};
		List<Map> list = mysqlcurd.SelectDataInt(sql, parameter);
		while(i<list.size()){
			Map map=list.get(i);
			try {
				Students s = new Students();
				s.setStuId(Integer.parseInt(map.get(0).toString()));
				s.setName(map.get(1).toString());
				s.setNumber(map.get(2).toString());
				s.setSex(Integer.parseInt(map.get(3).toString()));
				s.setDegree(Integer.parseInt(map.get(4).toString()));
				s.setIsleave(Integer.parseInt(map.get(5).toString()));
				s.setLeaveDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(6).toString()));		
				s.setTeaId(Integer.parseInt(map.get(7).toString()));
				studentOne.add(s);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			i++;
		}
		return studentOne;
	}
	
	/**
	 * @category 统计模糊查询的学生数量
	 */
		public int getCountModeStu(Teacher t,String stu)
		{
			int sum=0;
			String sql="select count(*) from students where teaId="+t.getTeaId()+" and name like '%"+stu+"%'";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
	
	/**
	 * @category 通过学号查询学生
	 */
	public Students selectStudentByStu(Students stu) {
		String sql= "select * from students where stuId=?";
		int i=0;
		Students s=new Students();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(stu.getStuId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		if(1==list.size()){
			Map map=list.get(0);
			try {
				s.setStuId(Integer.parseInt(map.get(0).toString()));
				s.setName(map.get(1).toString());
				s.setNumber(map.get(2).toString());
				s.setSex(Integer.parseInt(map.get(3).toString()));
				s.setDegree(Integer.parseInt(map.get(4).toString()));
				s.setIsleave(Integer.parseInt(map.get(5).toString()));
				s.setLeaveDate(new SimpleDateFormat("yyyy-MM-dd").parse(map.get(6).toString()));		
				s.setTeaId(Integer.parseInt(map.get(7).toString()));
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
	
		}
		return s;
	}
	
	/**
	 * @category 通过学生查询项目
	 */
	public List<Stuproject> selectProByStu(Students s) {
		String sql= "select * from stuproject where stuId=?";
		int i=0;
		List<Stuproject> stuprojectOne=new ArrayList<Stuproject>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(s.getStuId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		while(i<list.size()){
			Map map=list.get(i);
			try {
				Stuproject sp = new Stuproject();
				sp.setStuproId(Integer.parseInt(map.get(0).toString()));
				sp.setProname(map.get(1).toString());
				sp.setProlevel(Integer.parseInt(map.get(2).toString()));
				sp.setProsource(map.get(3).toString());
				sp.setStuId(Integer.parseInt(map.get(4).toString()));
				stuprojectOne.add(sp);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			i++;
		}
		return stuprojectOne;
	}
	
	/**
	 * @category 通过项目id查询项目
	 */
	public Stuproject selectProById(Stuproject s) {
		String sql= "select * from stuproject where stuproId=?";
		int i=0;
		Stuproject stuproject=new Stuproject();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(s.getStuproId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		if(1==list.size()){
			Map map=list.get(0);
			try {
				stuproject.setStuproId(Integer.parseInt(map.get(0).toString()));
				stuproject.setProname(map.get(1).toString());
				stuproject.setProlevel(Integer.parseInt(map.get(2).toString()));
				stuproject.setProsource(map.get(3).toString());
				stuproject.setStuId(Integer.parseInt(map.get(4).toString()));
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		
		}
		return stuproject;
	}
	
	/**
	 * @category 通过学生查询奖励
	 */
	public List<Stuaward> selectAwaByStu(Students s) {
		String sql= "select * from stuaward where stuId=?";
		int i=0;
		List<Stuaward> stuawardOne=new ArrayList<Stuaward>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(s.getStuId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		while(i<list.size()){
			Map map=list.get(i);
			try {
				Stuaward sa = new Stuaward();
				sa.setStuawaId(Integer.parseInt(map.get(0).toString()));
				sa.setAwaname(map.get(1).toString());
				sa.setAwalevel(Integer.parseInt(map.get(2).toString()));
				sa.setAwakind(Integer.parseInt(map.get(3).toString()));
				sa.setDepartment(map.get(4).toString());
				sa.setStuId(Integer.parseInt(map.get(5).toString()));
				stuawardOne.add(sa);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			i++;
		}
		return stuawardOne;
	}
	
	/**
	 * @category 通过奖励id查询奖励
	 */
	public Stuaward selectAwaById(Stuaward s) {
		String sql= "select * from stuaward where stuawaId=?";
		int i=0;
		Stuaward stuaward=new Stuaward();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(s.getStuawaId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		if(1==list.size()){
			Map map=list.get(0);
			try {
				stuaward.setStuawaId(Integer.parseInt(map.get(0).toString()));
				stuaward.setAwaname(map.get(1).toString());
				stuaward.setAwalevel(Integer.parseInt(map.get(2).toString()));
				stuaward.setAwakind(Integer.parseInt(map.get(3).toString()));
				stuaward.setDepartment(map.get(4).toString());
				stuaward.setStuId(Integer.parseInt(map.get(5).toString()));
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return stuaward;
	}
	
	/**
	 * @category 通过学生查询学位论文
	 */
	public Studegreepaper selectDegByStu(Students s) {
		String sql= "select * from studegreepaper where stuId=?";
		int i=0;
		Studegreepaper studegreepaper=new Studegreepaper();
		MysqlCrud mysqlcurd=new MysqlCrud();
		String[] parameter={String.valueOf(s.getStuId())};
		List<Map> list = mysqlcurd.SelectData(sql, parameter);
		if(1==list.size()){
			Map map=list.get(0);
			try {
				studegreepaper.setDegId(Integer.parseInt(map.get(0).toString()));
				studegreepaper.setDegname(map.get(1).toString());
				studegreepaper.setDegaward(Integer.parseInt(map.get(2).toString()));
				studegreepaper.setUpload(map.get(3).toString());
				studegreepaper.setStuId(Integer.parseInt(map.get(4).toString()));
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		
		}
		return studegreepaper;
	}
	
	/**
	 * @category 学生信息的修改
	 */
	public boolean updateStudent(Students s1,Students s2) {
		String sql= "update students set stuId=?,name=?,number=?,sex=?,degree=?,isleave=?,leaveDate=?,teaId=? where stuId="+s2.getStuId()+"";
		String[] parameter = {
				String.valueOf(s1.getStuId()),
				String.valueOf(s1.getName()),
				String.valueOf(s1.getNumber()),
				String.valueOf(s1.getSex()),
				String.valueOf(s1.getDegree()),
				String.valueOf(s1.getIsleave()),
				String.valueOf(new StringDate().DateToString(s1.getLeaveDate())),
				String.valueOf(s1.getTeaId())
		};
		MysqlCrud mysqlcurd=new MysqlCrud();
		boolean flag= mysqlcurd.InsertAndUpdateData(sql, parameter);
		return flag;
	}
	
	/**
	 * @category 统计一个老师学生的数量
	 */
		public int getCountStu(Teacher t)
		{
			int sum=0;
			String sql="select count(*) from students where teaId="+t.getTeaId()+"";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		
		/**
		 * @category 删除一个学生
		 */
		public boolean deleteStu(Students s){
			String sql="delete from students where stuId="+s.getStuId()+"";
			MysqlCrud mysqlcurd=new MysqlCrud();
			if(mysqlcurd.InsertAndUpdateData(sql, null)){
				return true;
			}else{
				return false;
			}
			
		}
}
